# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt
import frappe
from frappe import _, scrub
from frappe.model.document import Document
from frappe.utils import flt, nowdate, getdate
from frappe.utils.background_jobs import enqueue
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions
import openpyxl
import csv
from io import BytesIO

class TaxInvoiceImportToolCN(Document):
    def onload(self):
        summary, max_count = self.get_invoice_summary()
        self.set_onload("invoice_summary", summary)
        self.set_onload("max_count", max_count)
        self.set_onload("temporary_opening_account", self.get_temporary_opening_account(self.company))

    def get_invoice_summary(self):
        invoice_summary = {}
        max_count = {}
        fields = [
            "company",
            "count(name) as total_invoices",
            "sum(outstanding_amount) as outstanding_amount",
        ]
        companies = frappe.get_all("Company", fields=["name as company", "default_currency as currency"])
        if not companies:
            return None, None
            
        company_wise_currency = {row.company: row.currency for row in companies}
        invoices = frappe.get_all(
            "Sales Invoice", filters=dict(docstatus=1), fields=fields, group_by="company"
        )
        
        for invoice in invoices:
            company = invoice.pop("company")
            _summary = invoice_summary.get(company, {})
            _summary.update({
                "currency": company_wise_currency.get(company),
                "Sales Invoice": invoice
            })
            invoice_summary.update({company: _summary})
            
        return invoice_summary, max_count

    def validate_company(self):
        if not self.company:
            frappe.throw(_("Please select Company"))

    def set_missing_values(self, row):
        row.qty = row.qty or 1.0
        row.temporary_opening_account = row.temporary_opening_account or self.get_temporary_opening_account(self.company)
        row.party_type = "Customer" if self.invoice_type == "Sales" else "Supplier"
        row.item_name = row.item_name or _("Tax Invoice Item")
        row.posting_date = row.posting_date or nowdate()
        row.due_date = row.due_date or row.posting_date

    def validate_mandatory_invoice_fields(self, row):
        if not frappe.db.exists(row.party_type, row.party):
            if self.create_missing_party:
                self.add_party(row.party_type, row.party)
            else:
                frappe.throw(
                    _("Row #{0}: {1} {2} does not exist.").format(
                        row.idx, frappe.bold(row.party_type), frappe.bold(row.party)
                    )
                )

        mandatory_error_msg = _("Row #{0}: {1} is required to create Sales Invoice")
        for d in ("Party", "Outstanding Amount", "Temporary Opening Account"):
            if not row.get(scrub(d)):
                frappe.throw(mandatory_error_msg.format(row.idx, d))

    def get_invoices(self):
        invoices = []
        for row in self.invoices:
            if not row:
                continue
            self.set_missing_values(row)
            self.validate_mandatory_invoice_fields(row)
            invoice = self.get_invoice_dict(row)
            
            company_details = frappe.get_cached_value(
                "Company", self.company, ["default_currency", "default_letter_head"], as_dict=1
            ) or {}
            
            default_currency = frappe.db.get_value(row.party_type, row.party, "default_currency")
            if company_details:
                invoice.update({
                    "currency": default_currency or company_details.get("default_currency"),
                    "letter_head": company_details.get("default_letter_head"),
                })
                
            invoices.append(invoice)
        return invoices

    def add_party(self, party_type, party):
        party_doc = frappe.new_doc(party_type)
        party_doc.customer_name = party if party_type == "Customer" else party
        party_doc.supplier_name = party if party_type == "Supplier" else party
        party_doc.flags.ignore_mandatory = True
        party_doc.save(ignore_permissions=True)

    def get_invoice_dict(self, row=None):
        def get_item_dict():
            cost_center = row.get("cost_center") or frappe.get_cached_value("Company", self.company, "cost_center")
            if not cost_center:
                frappe.throw(_("Please set default Cost Center in Company {0}").format(frappe.bold(self.company)))
                
            income_expense_account_field = "income_account" if self.invoice_type == "Sales" else "expense_account"
            default_uom = frappe.db.get_single_value("Stock Settings", "stock_uom") or _("Nos")
            rate = flt(row.outstanding_amount) / flt(row.qty)
            
            item_dict = frappe._dict({
                "uom": default_uom,
                "rate": rate or 0.0,
                "qty": row.qty,
                "conversion_factor": 1.0,
                "item_name": row.item_name or "Tax Invoice Item",
                "description": row.item_name or "Tax Invoice Item",
                income_expense_account_field: row.temporary_opening_account,
                "cost_center": cost_center,
            })
            return item_dict

        item = get_item_dict()
        invoice = frappe._dict({
            "items": [item],
            "is_opening": "No",
            "set_posting_time": 1,
            "company": self.company,
            "cost_center": self.cost_center,
            "due_date": row.due_date,
            "posting_date": row.posting_date,
            frappe.scrub(row.party_type): row.party,
            "is_pos": 0,
            "doctype": "Sales Invoice" if self.invoice_type == "Sales" else "Purchase Invoice",
            "update_stock": 0,
            "invoice_number": row.invoice_number,
            "disable_rounded_total": 1,
        })
        
        for dimension in get_accounting_dimensions():
            invoice.update({dimension: self.get(dimension) or item.get(dimension)})
            
        return invoice

    def get_temporary_opening_account(self, company=None):
        if not company:
            return
        accounts = frappe.get_all("Account", filters={"company": company, "account_type": "Temporary"})
        if not accounts:
            frappe.throw(_("Please add a temporary account in the Chart of Accounts"))
        return accounts[0].name

    @frappe.whitelist()
    def make_invoices(self):
        invoices = self.get_invoices()
        enqueue("erpnext.accounts.doctype.tax_invoice_import_tool.tax_invoice_import_tool.create_invoices",
            invoices=invoices, is_async=True)
        frappe.msgprint(_("Invoices creation has been queued."))

    @staticmethod
    def create_invoices(invoices):
        for invoice in invoices:
            doc = frappe.get_doc(invoice)
            doc.insert(ignore_permissions=True)
            doc.submit()

    @frappe.whitelist()
    def read_excel_or_csv_file(self, file_id):
        file_doc = frappe.get_doc("File", file_id)
        file_content = file_doc.get_content()

        if file_id.endswith(('.xlsx', '.xls')):
            return self._read_excel(file_content)
        elif file_id.endswith('.csv'):
            return self._read_csv(file_content)
        else:
            frappe.throw(_("Unsupported file format. Only Excel (.xlsx, .xls) and CSV (.csv) are allowed."))

    def _read_excel(self, file_content):
        wb = openpyxl.load_workbook(BytesIO(file_content), data_only=True)
        ws = wb.active
        header_row = None
        column_mapping = {}

        for row in ws.iter_rows(min_row=1, max_row=10):
            for cell in row:
                if cell.value and "Invoice Number" in str(cell.value):
                    header_row = cell.row
                    break
            if header_row:
                break

        if not header_row:
            frappe.throw(_("Could not find header row"))

        for cell in ws[header_row]:
            if cell.value:
                value = str(cell.value).strip()
                if value == "Invoice Number":
                    column_mapping["invoice_number"] = cell.column_letter
                elif value == "Party":
                    column_mapping["party"] = cell.column_letter
                elif value == "Posting Date":
                    column_mapping["posting_date"] = cell.column_letter
                elif value == "Due Date":
                    column_mapping["due_date"] = cell.column_letter
                elif value == "Item Name":
                    column_mapping["item_name"] = cell.column_letter
                elif value == "Outstanding Amount":
                    column_mapping["outstanding_amount"] = cell.column_letter
                elif value == "Currency":
                    column_mapping["currency"] = cell.column_letter

        required_columns = ["invoice_number", "party", "posting_date", "item_name", "outstanding_amount", "currency"]
        missing_columns = [col for col in required_columns if col not in column_mapping]
        if missing_columns:
            frappe.throw(_("Excel file is missing required columns: {0}").format(", ".join(missing_columns)))

        data = []
        for row_num in range(header_row + 1, ws.max_row + 1):
            row_data = {
                "party_type": "Customer",
                "qty": 1.0,
                "currency": ws[f"{column_mapping.get('currency', '')}{row_num}"].value or "CNY"
            }
            row_data["invoice_number"] = ws[f"{column_mapping['invoice_number']}{row_num}"].value
            row_data["party"] = ws[f"{column_mapping['party']}{row_num}"].value
            date_value = ws[f"{column_mapping['posting_date']}{row_num}"].value
            if date_value:
                row_data["posting_date"] = date_value.strftime("%Y-%m-%d") if not isinstance(date_value, str) else date_value
            due_date_value = ws[f"{column_mapping.get('due_date', '')}{row_num}"].value
            if due_date_value:
                row_data["due_date"] = due_date_value.strftime("%Y-%m-%d") if not isinstance(due_date_value, str) else due_date_value
            row_data["item_name"] = ws[f"{column_mapping['item_name']}{row_num}"].value
            row_data["outstanding_amount"] = flt(ws[f"{column_mapping['outstanding_amount']}{row_num}"].value or 0)
            if row_data["invoice_number"] and row_data["party"]:
                data.append(row_data)
        return data

    def _read_csv(self, file_content):
        content = file_content.decode("utf-8")
        reader = csv.DictReader(content.splitlines())
        required_columns = ["Invoice Number", "Party", "Posting Date", "Item Name", "Outstanding Amount", "Currency"]
        missing_columns = [col for col in required_columns if col not in reader.fieldnames]
        if missing_columns:
            frappe.throw(_("CSV file is missing required columns: {0}").format(", ".join(missing_columns)))

        data = []
        for row in reader:
            row_data = {
                "party_type": "Customer",
                "qty": 1.0,
                "invoice_number": row["Invoice Number"],
                "party": row["Party"],
                "posting_date": row["Posting Date"],
                "due_date": row.get("Due Date"),
                "item_name": row["Item Name"],
                "outstanding_amount": flt(row["Outstanding Amount"] or 0),
                "currency": row["Currency"] or "CNY"
            }
            if row_data["invoice_number"] and row_data["party"]:
                data.append(row_data)
        return data